﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Edit_ImportQuotation : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        private List<int> change_row;

        public string partno;
        public string supplierid;

        public frm_Edit_ImportQuotation()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);

            this.change_row = new List<int>();

            this.partno = null;
            this.supplierid = null;


            LoadData();
            EditQuotation.CellValueChanged += ChangeQuantity_CellValueChanged;
        }

        void ChangeQuantity_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            EditQuotation.PostEditor();
            EditQuotation.UpdateCurrentRow();
            change_row.Add(EditQuotation.FocusedRowHandle);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        public bool LoadData()
        {
            bool ret = true;

            if (string.IsNullOrEmpty(partno) || string.IsNullOrEmpty(supplierid))
            {
                ret = false;
            }
            else
            {
                DataSet sourceDataSet = new DataSet();
                INISettings mySettings = new INISettings();
                SQLManagement mySQLManagement = new SQLManagement();
                mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
                string sql = "SELECT t1.[ID], t1.[Date], t1.[PartNo], t1.[Quantity], t1.[Price], t5.[UnitNameEN],"
                    + " t1.[SupplierID], t4.[SupplierName], t3.[EnglishName], t6.[CurrencyName], t1.[Note]"
                    + " FROM [241_ImportQuotation] AS t1 LEFT OUTER JOIN"
                    + " [222_Part] AS t2 ON t1.[PartNo]=t2.[PartNo] LEFT OUTER JOIN"
                    + " [222_Maker] AS t3 ON t3.[MakerID]=t2.[MakerID] LEFT OUTER JOIN"
                    + " [222_Supplier] AS t4 ON t4.[SupplierID]=t1.[SupplierID] LEFT OUTER JOIN"
                    + " [222_Unit] AS t5 ON t5.[UnitID]=t2.[UnitID] LEFT OUTER JOIN"
                    + " [222_Currency] AS t6 ON t6.[CurrencyID]=t1.[CurrencyID]"
                    + " WHERE t1.[Old] = 0 AND t1.[PartNo]='" + partno + "' AND t1.[SupplierID]='" + supplierid + "'"
                    + " ORDER BY t1.[PartNo], t1.[Quantity]";
                sourceDataSet = mySQLManagement.SQL_Query(sql);
                if (sourceDataSet != null)
                {
                    gridEditQuotation.DataSource = sourceDataSet.Tables[0];
                }
                else
                {
                    MessageBox.Show("There is no data for Import Quotation");
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("ID", typeof(int));
                    newTable.Columns.Add("Date", typeof(string));
                    newTable.Columns.Add("PartNo", typeof(int));
                    newTable.Columns.Add("Quantity", typeof(string));
                    newTable.Columns.Add("Price", typeof(string));
                    newTable.Columns.Add("Unit", typeof(string));
                    newTable.Columns.Add("SupplierID", typeof(string));
                    newTable.Columns.Add("Supplier", typeof(string));
                    newTable.Columns.Add("Maker", typeof(string));
                    newTable.Columns.Add("Currency", typeof(string));
                    newTable.Columns.Add("Note", typeof(string));
                    gridEditQuotation.DataSource = newTable;

                    ret = false;
                }
                EditQuotation.Columns[0].Caption = "ID";
                EditQuotation.Columns[1].Caption = "Date";
                EditQuotation.Columns[2].Caption = "Part No";
                EditQuotation.Columns[3].Caption = "Quantity";
                EditQuotation.Columns[4].Caption = "Price";
                EditQuotation.Columns[5].Caption = "Unit";
                EditQuotation.Columns[6].Caption = "SupplierID";
                EditQuotation.Columns[6].Visible = false;
                EditQuotation.Columns[7].Caption = "Supplier";
                EditQuotation.Columns[8].Caption = "Maker";
                EditQuotation.Columns[9].Caption = "Currency";
                EditQuotation.Columns[10].Caption = "Note";
                EditQuotation.Columns[0].Visible = false;
                EditQuotation.BestFitColumns();
                EditQuotation.Columns[0].OptionsColumn.AllowEdit = false;
                EditQuotation.Columns[1].OptionsColumn.AllowEdit = false;
                EditQuotation.Columns[2].OptionsColumn.AllowEdit = false;
                EditQuotation.Columns[5].OptionsColumn.AllowEdit = false;
                EditQuotation.Columns[6].OptionsColumn.AllowEdit = false;
                EditQuotation.Columns[7].OptionsColumn.AllowEdit = false;
                EditQuotation.Columns[8].OptionsColumn.AllowEdit = false;
                EditQuotation.Columns[9].OptionsColumn.AllowEdit = false;
            }

            return ret;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonUpdate_Click(object sender, EventArgs e)
        {
            UpdateChange();
        }

        private void UpdateChange()
        {
            DateTime input_date = DateTime.Now;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                foreach (int i in change_row)
                {
                    DataRow row = EditQuotation.GetDataRow(i);
                    cmd_sql.CommandText = "UPDATE [241_ImportQuotation] SET Old=1 WHERE id=" + row[0].ToString();
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                        + " Quantity, Price, CurrencyID, Note)"
                        + " VALUES ('" + input_date.Date.ToString("yyyyMMdd") + "', '" + row[2].ToString() + "',"
                        + " '" + row[6].ToString() + "', " + row[3].ToString() + ", " + row[4].ToString() + ","
                        + " " + row[9].ToString() + ", '" + row[10].ToString() + "')";
                    cmd_sql.ExecuteNonQuery();
                }
                cmd_sql.Dispose();
                myConnection.Close();

                MessageBox.Show("Update successfully!");
                LoadData();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot update database");
            }
        }       
    }
}
